USE [Test_Events]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetNewsArticleSources]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetNewsArticleSources]
GO

CREATE PROCEDURE [dbo].[spGetNewsArticleSources]
	@EventIndicators NVARCHAR(MAX),
	@Quarter DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @EventIndicators, 1)

	DECLARE @SelectedEventIndicators TABLE
	(
		CompanyId uniqueidentifier NOT NULL,
		BaseEventIndicatorId uniqueidentifier NOT NULL,
		EventIndicatorAssessment INT NOT NULL
	)
	INSERT INTO @SelectedEventIndicators
	SELECT TC.c.value('.','uniqueidentifier') AS CompanyId,
		   TB.bei.value('.','uniqueidentifier') AS BaseEventIndicatorId,
		   TA.eia.value('.','int') AS EventIndicatorAssessment
	FROM @XML.nodes('/eis/ei') AS T(ei) 
		outer apply T.ei.nodes('c') as TC(c) 
		outer apply T.ei.nodes('bei') as TB(bei)
		outer apply T.ei.nodes('eia') as TA(eia)

	DECLARE @Sources TABLE
	(
		ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		Quater DATETIME NOT NULL,

		EventIndicatorAssessment INT NOT NULL,
		
		PublicationDate DATETIME NOT NULL,
		NewsArticleName NVARCHAR(450) NOT NULL, -- 377 max
		NewsArticleSource NVARCHAR(450) NULL, -- 75max

		IncidentDate DATETIME NOT NULL,
		IncidentName NVARCHAR(450) NOT NULL,
		IncidentSource NVARCHAR(450) NULL
	)


	INSERT INTO @Sources
	SELECT  dbo.EventIndicatorCompanyProfiles.CompanyId,
			@Quarter AS Quater,

			SelectedEventIndicators.EventIndicatorAssessment,

			dbo.NewsArticles.PublicationDate,
			dbo.NewsArticles.Headline AS NewsArticleName,
			dbo.NewsSourceNames.Name AS SourceName,

			dbo.Incidents.IncidentDate,
			dbo.Incidents.Name AS IncidentName,
			dbo.Incidents.Source AS IncidentSource

	FROM	dbo.EventIndicatorCompanyProfiles INNER JOIN
			dbo.EventIndicators ON dbo.EventIndicatorCompanyProfiles.Id = dbo.EventIndicators.CompanyProfileId INNER JOIN
			@SelectedEventIndicators AS SelectedEventIndicators ON dbo.EventIndicatorCompanyProfiles.CompanyId = SelectedEventIndicators.CompanyId AND dbo.EventIndicators.BaseEventIndicatorId = SelectedEventIndicators.BaseEventIndicatorId INNER JOIN
			dbo.EventIndicatorIncidents ON dbo.EventIndicators.Id = dbo.EventIndicatorIncidents.EventIndicatorId INNER JOIN
			dbo.Incidents ON dbo.EventIndicatorIncidents.IncidentId = dbo.Incidents.Id INNER JOIN
			dbo.NewsArticles ON dbo.Incidents.NewsArticleId = dbo.NewsArticles.Id INNER JOIN
			dbo.NewsSourceNames ON dbo.NewsArticles.SourceNameId = dbo.NewsSourceNames.Id
	WHERE (dbo.EventIndicatorCompanyProfiles.Status = 0)
    GROUP BY dbo.EventIndicatorCompanyProfiles.CompanyId, SelectedEventIndicators.EventIndicatorAssessment, dbo.NewsArticles.PublicationDate, dbo.NewsArticles.Headline, dbo.Incidents.Name, dbo.Incidents.IncidentDate, dbo.Incidents.Source, dbo.NewsSourceNames.Name


	DECLARE @ID int
	DECLARE @Count int

	SELECT @ID = MIN(ID) FROM @Sources
	SELECT @Count = MAX(ID) FROM @Sources

	DECLARE @CompanyId UNIQUEIDENTIFIER
	DECLARE @Quater DATETIME
	DECLARE @EventIndicatorAssessment INT
	DECLARE @PublicationDate DATETIME, @IncidentDate DATETIME
	DECLARE @NewsArticleName NVARCHAR(450), @IncidentName NVARCHAR(450)
	DECLARE @NewsArticleSource NVARCHAR(450), @IncidentSource NVARCHAR(450)

	DECLARE @FilteredSources TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		Quater DATETIME NOT NULL,
		Type INT NOT NULL,
		EventIndicatorAssessment INT NOT NULL,
		PublicationDate DATETIME NOT NULL,
		NewsArticleName NVARCHAR(450) NOT NULL,
		SourceName NVARCHAR(450) NULL
	)

	WHILE @ID <= @Count BEGIN

		SELECT @CompanyId = CompanyId, 
		       @Quater = Quater,
			   @EventIndicatorAssessment = EventIndicatorAssessment,
			   @PublicationDate = PublicationDate,
			   @NewsArticleName = NewsArticleName,
			   @NewsArticleSource = NewsArticleSource,
			   @IncidentDate = IncidentDate,
			   @IncidentName = IncidentName,
			   @IncidentSource = IncidentSource
		FROM @Sources
		WHERE ID = @ID

		IF (@NewsArticleName LIKE N'%fake%')
		OR (@NewsArticleName LIKE N'%fake source%') 
		OR (@NewsArticleName LIKE N'%incidents import%')
		OR (@NewsArticleName LIKE N'%fake news article%')
		OR (@NewsArticleName LIKE N'%incident transition%')
		OR (@NewsArticleName LIKE N'%GCCS transition%')
		OR (@NewsArticleName LIKE N'%Incident upload%')
		OR (@NewsArticleName LIKE N'%fake news%')
		OR (@NewsArticleName LIKE N'%test news%') BEGIN
			SET @PublicationDate = @IncidentDate
			SET @NewsArticleName = @IncidentName
			SET @NewsArticleSource = @IncidentSource
		 END

		INSERT INTO @FilteredSources
		VALUES (@CompanyId, @Quater, 2, @EventIndicatorAssessment, @PublicationDate, @NewsArticleName, @NewsArticleSource)

		SET @ID = @ID + 1
	END

	SELECT * FROM @FilteredSources
	GROUP BY Quater, CompanyId, Type, EventIndicatorAssessment, NewsArticleName, PublicationDate, SourceName

END

GO


